import { Router } from "https://deno.land/x/oak@v12.6.0/mod.ts";
import { client, closeDB } from "../db.ts";

const linkRouter = new Router();

// 添加链接
linkRouter.post("/addRaccoonLink", async (ctx) => {
  try {
    const { name, info, url, userId } = await ctx.request.body().value;

    if (!name || !url) {
      ctx.response.body = { message: "Name and URL are required" };
      ctx.response.status = 400;
      return;
    }

    const db = await client();
    db.query(`
      INSERT INTO raccoon_link (name, info, url, user_id, status, add_dtme, positive_points, negative_points)
      VALUES (${name}, ${info}, ${url}, ${userId}, true, NOW(), 0, 0)`);
    ctx.response.body = { message: "Link added successfully" };
  } catch (error) {
    console.error(`Error in /addRaccoonLink: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  }
});

linkRouter.get("/getRaccoonLink", async (ctx) => {
  console.log('Request received for /getRaccoonLink');
  const url = new URL(ctx.request.url);
  const userId = url.searchParams.get('userId');
  try {
    const db = await client();


    const priorResult = await db.query(`
      SELECT AVG(positive_points - negative_points) AS global_avg_rating
      FROM raccoon_link
      WHERE status = true
    `);

    const globalAvgRating = priorResult[0]?.[0] || 0;

    if (!userId) {
      const sql = `
        SELECT a.id, a.name, a.info, a.url, a.user_id,
               CASE 
                 WHEN (positive_points - negative_points) < 0 THEN 0 
                 ELSE (positive_points - negative_points) 
               END AS raw_ratings,
               ((${globalAvgRating} * 10) + ((positive_points - negative_points) * COUNT(*) ) ) / (COUNT(*) + 10) AS bayesian_ratings
        FROM raccoon_link a
        LEFT JOIN user_ratings b ON b.link_id = a.id AND b.rating_type != 'dislike'
        WHERE a.status = true
        GROUP BY a.id, a.name, a.info, a.url, a.user_id, positive_points, negative_points
        ORDER BY bayesian_ratings DESC;
      `;
      const result = await db.query(sql);
      const mappedResults = result.map(row => {
        return {
          id: row[0],
          name: row[1],
          info: row[2],
          url: row[3],
          userId: row[4],
          ratings: row[5],
          bayesian_ratings: row[6]
        };
      });
      ctx.response.body = { data: mappedResults };
    }

    // If userId is provided, modify the query to consider user ratings
    if (userId) {
      const sql = `
        SELECT a.id, a.name, a.info, a.url, a.user_id,
               CASE 
                 WHEN (positive_points - negative_points) < 0 THEN 0 
                 ELSE (positive_points - negative_points) 
               END AS raw_ratings,
               ((${globalAvgRating} * 10) + ((positive_points - negative_points) * COUNT(*) ) ) / (COUNT(*) + 10) AS bayesian_ratings
        FROM raccoon_link a
        LEFT JOIN user_ratings b 
          ON b.link_id = a.id 
          AND b.rating_type != 'dislike' 
          AND b.user_id = ?
        WHERE a.status = true
        AND NOT EXISTS (
          SELECT 1
          FROM user_ratings c
          WHERE c.link_id = a.id
            AND c.user_id = ?
            AND c.rating_type = 'dislike'
        )
        GROUP BY a.id, a.name, a.info, a.url, a.user_id, positive_points, negative_points
        ORDER BY bayesian_ratings DESC;
      `;
      const result = await db.query(sql, [userId, userId]);
      const mappedResults = result.map(row => {
        return {
          id: row[0],
          name: row[1],
          info: row[2],
          url: row[3],
          userId: row[4],
          ratings: row[5],
          bayesian_ratings: row[6]
        };
      });
      ctx.response.body = { data: mappedResults };
    }

  } catch (error) {
    console.error(`Error in /getRaccoonLink: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  }
});



// 取消链接
linkRouter.post("/cancelLink", async (ctx) => {
  const { id } = await ctx.request.body().value;
  if (!id) {
    ctx.response.body = { message: "ID is required" };
    ctx.response.status = 400;
    return;
  }

  const db = await client();
  try {
    db.query(`UPDATE raccoon_link SET status = false WHERE id = ${id}`)
    ctx.response.body = { message: "Link cancelled successfully" };
  } catch (error) {
    console.error(`Error in /cancelLink: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  } finally {
    closeDB(db)
  }
});

//添加连接
linkRouter.post("/submitNewLink", async (ctx) => {
  const { userId, name, info, url } = await ctx.request.body().value;

  if (!name || !info || !url) {
    ctx.response.body = { message: "Link Name or link info or link url is required" };
    ctx.response.status = 400;
    return;
  }
  const db = await client();
  try {
    const currentTime = new Date();
    db.query('insert into raccoon_link (name, info,url,user_id,created_at,status)values(?,?,?,?,?,?)', [name, info, url, userId, currentTime, true]);
    ctx.response.body = { message: "Link add successfully" };
  } catch (error) {
    console.error(`Error in /submitNewLink: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  } finally {
    closeDB(db)
  }

});

// 增加正向评分（点赞）
linkRouter.post("/addPositivePoint", async (ctx) => {
  const { link_id, userId } = await ctx.request.body().value;

  if (!link_id || !userId) {
    ctx.response.body = { message: "Link ID and User ID are required" };
    ctx.response.status = 400;
    return;
  }
  const db = await client();
  try {
    // 检查用户是否已经点过赞
    const existingLike = db.query(`SELECT * FROM user_ratings WHERE user_id = ${userId} AND link_id = ${link_id} AND rating_type = 'like'`);
    if (existingLike.length > 0) {
      ctx.response.body = { message: "You have already liked this link." };
      ctx.response.status = 400;
      return;
    }

    // 如果用户未点赞，插入点赞记录
    await db.query(`INSERT INTO user_ratings (user_id, link_id, rating_type) VALUES (${userId}, ${link_id}, 'like')`);

    // 更新链接的正向评分
    await db.query(`
      UPDATE raccoon_link
      SET positive_points = positive_points + 1
      WHERE id = ${link_id}`);

    ctx.response.body = { message: "Positive point added successfully" };
  } catch (error) {
    console.error(`Error in /addPositivePoint: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  } finally {
    closeDB(db)
  }
});


// 增加逆向评分（点踩）
linkRouter.post("/addNegativePoint", async (ctx) => {
  const { link_id, userId } = await ctx.request.body().value;

  if (!link_id || !userId) {
    ctx.response.body = { message: "Link ID and User ID are required" };
    ctx.response.status = 400;
    return;
  }
  const db = await client();
  try {

    // 检查用户是否已经点过踩
    const existingDislike = await db.query(`
      SELECT * FROM user_ratings
      WHERE user_id = ${userId} AND link_id = ${link_id} AND rating_type = 'dislike'`);

    if (existingDislike.length > 0) {
      ctx.response.body = { message: "You have already disliked this link." };
      ctx.response.status = 400;
      return;
    }

    // 如果用户未点踩，插入点踩记录
    await db.query(`
      INSERT INTO user_ratings (user_id, link_id, rating_type)
      VALUES (${userId}, ${link_id}, 'dislike')`);


    // 更新链接的逆向评分
    await db.query(`
      UPDATE raccoon_link
      SET negative_points = negative_points + 1
      WHERE id = ${link_id}`);

    console.log(444444)

    ctx.response.body = { message: "Negative point added successfully" };
  } catch (error) {
    console.error(`Error in /addNegativePoint: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  }
});


// 获取用所有linked
linkRouter.get("/getUserLikedLinks", async (ctx) => {
  const url = new URL(ctx.request.url);
  const userId = url.searchParams.get('userId');
  if (!userId) {
    ctx.response.body = { message: "User ID are required" };
    ctx.response.status = 400;
    return;
  }
  const db = await client();
  try {

    const result = db.query(
      `SELECT id, name, info, url
       FROM raccoon_link
       WHERE status = 1 
         AND id IN (
           SELECT link_id
           FROM user_ratings
           WHERE rating_type = 'like'
             AND user_id = ?
         );`,
      [userId]
    );
    const mappedResults = result.map(row => {
      return {
        id: row[0],
        name: row[1],
        info: row[2],
        url: row[3],
      };
    });
    ctx.response.body = { data: mappedResults };
  } catch (error) {
    console.error(`Error in /getUserLikedLinks: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  } finally {
    closeDB(db)
  }
});

// 移除所有linked
linkRouter.post("/removeStar", async (ctx) => {
  const { link_id, userId } = await ctx.request.body().value;
  if (!link_id || !userId) {
    ctx.response.body = { message: "LinkId or User ID are required" };
    ctx.response.status = 400;
    return;
  }
  const db = await client();
  try {
    db.query("delete from user_ratings where user_id=? and link_id=?", [userId, link_id])
    ctx.response.body = { message: "Star removed successfully" };
  } catch (error) {
    console.error(`Error in /removeStar: ${error.message}`);
    ctx.response.status = 500;
    ctx.response.body = { message: "Internal Server Error", error: error.message };
  } finally {
    closeDB(db)
  }
});



export default linkRouter;
